//
//  SQLiteManager.m
//  Rich
//
//  Created by Kenny Chen on 12/12/7.
//  Copyright (c) 2012年 Kenny Chen. All rights reserved.
//

#import "SQLiteManager.h"
#import "RichError.h"

#pragma mark - SQLite Manager

@interface SQLiteManager ()

@end

@implementation SQLiteManager

@synthesize dbStruct;
@synthesize dbPath;
@synthesize isOpen;

- (id)init
{
    self = [super init];
    if (self) {
        dbStruct = nil;
        dbPath = nil;
        isOpen = NO;
    }
    
    return self;
}

- (void)dealloc
{
    [super dealloc];
}

- (int)open:(NSString *)filename
{
    NSArray  *documentsPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *databaseFilePath = [[documentsPaths objectAtIndex:0] stringByAppendingPathComponent:filename];
    
    //NSLog(@"Path = %@", databaseFilePath);
    
    dbPath = [databaseFilePath copy];
    
    if (sqlite3_open_v2([databaseFilePath UTF8String], &database, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, NULL) == SQLITE_OK) {
        if (sqlite3_exec(database, "PRAGMA foreign_keys = ON;", 0, 0, 0) == SQLITE_OK) {
            dbStruct = [[NSMutableDictionary alloc] init];
            isOpen = YES;
            NSLog(@"Database %@ open successfully", filename);
            return RICH_OK;
        }
        else {
            NSLog(@"Unable to turn foreign keys on");
            sqlite3_close(database);
            return -ERROR_SQLITE_FOREIGN_KEY;
        }
    }
    else {
        NSLog(@"Database %@ open failed: %@", filename,
              [NSString stringWithCString:sqlite3_errmsg(database) encoding:NSStringEncodingConversionAllowLossy]);
        sqlite3_close(database);
        return -ERROR_SQLITE_OPEN;
    }
}

- (void)close
{
    for (NSString *key in dbStruct) {
        NSMutableDictionary *dict = [dbStruct objectForKey:key];
        [dict removeAllObjects];
    }
    
    [dbStruct removeAllObjects];
    [dbStruct release];
    dbStruct = nil;
    
    sqlite3_close(database);
    
    isOpen = NO;
}

- (int)execute:(NSString *)sql
{
    char *errorMsg;
    return sqlite3_exec(database, [sql UTF8String], NULL, NULL, &errorMsg);
}

- (NSMutableArray *)listTables
{
    NSMutableArray *tables = nil;
    SQLMResultSet *resultSet = [self selectColumns:@"name" fromTable:@"sqlite_master" where:@"type = \"table\""];
    
    if (resultSet) {
        if ([resultSet dataCount] == 0) return nil;
        
        tables = [[NSMutableArray alloc] initWithCapacity:[resultSet dataCount]];
        
        while ([resultSet next]) {
            [tables addObject:[resultSet stringForColumnIndex:0]];
        }
        
        [resultSet close];
        [resultSet release];
        
        return tables;
    }
    
    [resultSet close];
    [resultSet release];
    
    NSLog(@"Select error: %@", [NSString stringWithCString:sqlite3_errmsg(database) encoding:NSStringEncodingConversionAllowLossy]);
    
    return nil;
}

- (int)createTable:(NSString *)table columnDefinitions:(NSString *)columnDef
{
    char *errorMsg;
    NSString *sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@ (%@);", table, columnDef];
    
    //NSLog(@"SQL = %@", sql);
    
    if (sqlite3_exec(database, [sql UTF8String], NULL, NULL, &errorMsg) == SQLITE_OK) {
        NSMutableDictionary *_tableDict = [[NSMutableDictionary alloc] init];
        NSArray *columns = [columnDef componentsSeparatedByString:@","];
        
        for (NSString *def in columns) {
            NSArray *array = [[def stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceAndNewlineCharacterSet]] componentsSeparatedByString:@" "];
            if ([array count] < 2) {
                return -ERROR_SQLITE_INVALID_SQL;
            }
            
            NSString *name = [(NSString *)[array objectAtIndex:0] stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceAndNewlineCharacterSet]];
            NSString *type = [(NSString *)[array objectAtIndex:1] stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceAndNewlineCharacterSet]];
            [_tableDict setObject:type forKey:name];
        }
        
        [dbStruct setObject:_tableDict forKey:table];
        [_tableDict release];
        
        return RICH_OK;
    }
    
    NSLog(@"Create %@ error: %@", table, [NSString stringWithCString:sqlite3_errmsg(database) encoding:NSStringEncodingConversionAllowLossy]);
    sqlite3_free(errorMsg);
    
    return -ERROR_SQLITE_EXEC_SQL;
}

- (int64_t)insertColumns:(NSString *)columns withValues:(NSString *)values intoTable:(NSString *)table
{
    char *errorMsg;
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO %@ (%@) VALUES (%@);", table, columns, values];
    
    //NSLog(@"%@", sql);
    
    if (sqlite3_exec(database, [sql UTF8String], NULL, NULL, &errorMsg) == SQLITE_OK) {
        return sqlite3_last_insert_rowid(database);
    }
    
    NSLog(@"Insert %@ error: %@", table, [NSString stringWithCString:sqlite3_errmsg(database) encoding:NSStringEncodingConversionAllowLossy]);
    sqlite3_free(errorMsg);
    
    return -ERROR_SQLITE_EXEC_SQL;
}

- (int)updateTable:(NSString *)table setColumns:(NSString *)syntax where:(NSString *)conditions
{
    char *errorMsg;
    NSString *sql = [NSString stringWithFormat:@"UPDATE %@ SET %@ WHERE %@;", table, syntax, conditions];
    
    //NSLog(@"%@", sql);
    
    if (sqlite3_exec(database, [sql UTF8String], NULL, NULL, &errorMsg) == SQLITE_OK) {
        return RICH_OK;
    }
    
    NSLog(@"Update %@ error: %@", table, [NSString stringWithCString:sqlite3_errmsg(database) encoding:NSStringEncodingConversionAllowLossy]);
    sqlite3_free(errorMsg);
    
    return -ERROR_SQLITE_EXEC_SQL;
}

- (int)deleteFromTable:(NSString *)table where:(NSString *)conditions
{
    char *errorMsg;
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@;", table, conditions];
    
    //NSLog(@"%@", sql);
    
    if (sqlite3_exec(database, [sql UTF8String], NULL, NULL, &errorMsg) == SQLITE_OK) {
        return RICH_OK;
    }
    
    NSLog(@"Delete %@ error: %@", table, [NSString stringWithCString:sqlite3_errmsg(database) encoding:NSStringEncodingConversionAllowLossy]);
    sqlite3_free(errorMsg);
    
    return -ERROR_SQLITE_EXEC_SQL;
}

- (SQLMResultSet *)selectColumns:(NSString *)columns fromTable:(NSString *)table
{
    return [self selectColumns:columns fromTable:table where:nil orderBy:nil descending:NO limit:nil];
}

- (SQLMResultSet *)selectColumns:(NSString *)columns fromTable:(NSString *)table limit:(NSString *)limit
{
    return [self selectColumns:columns fromTable:table where:nil orderBy:nil descending:NO limit:limit];
}

- (SQLMResultSet *)selectColumns:(NSString *)columns fromTable:(NSString *)table orderBy:(NSString *)orderByColumn descending:(BOOL)descending
{
    return [self selectColumns:columns fromTable:table where:nil orderBy:orderByColumn descending:descending limit:nil];
}

- (SQLMResultSet *)selectColumns:(NSString *)columns fromTable:(NSString *)table orderBy:(NSString *)orderByColumn descending:(BOOL)descending limit:(NSString *)limit
{
    return [self selectColumns:columns fromTable:table where:nil orderBy:orderByColumn descending:descending limit:limit];
}

- (SQLMResultSet *)selectColumns:(NSString *)columns fromTable:(NSString *)table where:(NSString *)conditions
{
    return [self selectColumns:columns fromTable:table where:conditions orderBy:nil descending:NO limit:nil];
}

- (SQLMResultSet *)selectColumns:(NSString *)columns fromTable:(NSString *)table where:(NSString *)conditions orderBy:(NSString *)orderByColumn descending:(BOOL)descending
{
    return [self selectColumns:columns fromTable:table where:conditions orderBy:orderByColumn descending:descending limit:nil];
}

- (SQLMResultSet *)selectColumns:(NSString *)columns fromTable:(NSString *)table where:(NSString *)conditions limit:(NSString *)limit
{
    return [self selectColumns:columns fromTable:table where:conditions orderBy:nil descending:NO limit:limit];
}

- (SQLMResultSet *)selectColumns:(NSString *)columns fromTable:(NSString *)table where:(NSString *)conditions orderBy:(NSString *)orderByColumn descending:(BOOL)descending limit:(NSString *)limit
{
    NSString *countSql = [NSString stringWithFormat:@"SELECT COUNT(*) FROM %@%@%@;", table,
                           (conditions != nil) ? [NSString stringWithFormat:@" WHERE %@", conditions] : @"",
                           (limit != nil) ? [NSString stringWithFormat:@" LIMIT %@", limit] : @""];
    
    NSString *selectSql = [NSString stringWithFormat:@"SELECT %@ FROM %@%@%@%@%@;", columns, table,
                           (conditions != nil) ? [NSString stringWithFormat:@" WHERE %@", conditions] : @"",
                           (orderByColumn != nil) ? [NSString stringWithFormat:@" ORDER BY %@", orderByColumn] : @"",
                           (descending == YES) ? @" DESC" : @"",
                           (limit != nil) ? [NSString stringWithFormat:@" LIMIT %@", limit] : @""];
    
    //NSLog(@"Select SQL: %@", selectSql);
    
    SQLMResultSet *countResultSet = [[SQLMResultSet alloc] init];
    SQLMResultSet *dataResultSet = [[SQLMResultSet alloc] init];
    
    // Get result count first
    if (sqlite3_prepare_v2(database, [countSql UTF8String], -1, countResultSet.statement, nil) == SQLITE_OK) {
        [countResultSet next];
        dataResultSet.dataCount = [countResultSet intForColumnIndex:0];
        [countResultSet close];
        [countResultSet release];
    }
    
    if (sqlite3_prepare_v2(database, [selectSql UTF8String], -1, dataResultSet.statement, nil) == SQLITE_OK) {
        return dataResultSet;
    }
    
    [dataResultSet close];
    [dataResultSet release];
    
    NSLog(@"Select error: %@", [NSString stringWithCString:sqlite3_errmsg(database) encoding:NSStringEncodingConversionAllowLossy]);
    
    return nil;
}

@end

#pragma mark - SQLite Manager Result Set

@implementation SQLMResultSet

@synthesize statement;
@synthesize dataCount;

- (id)init
{
    self = [super init];
    if (self) {
        _columnNameToIndexMap = nil;
        statement = (sqlite3_stmt **)malloc(sizeof(sqlite3_stmt *));
        if (statement == NULL) {
            [self release];
            return nil;
        }
    }
    
    return self;
}

- (void)dealloc
{
    if (statement) {
        free(statement);
    }
    
    if (_columnNameToIndexMap) {
        [_columnNameToIndexMap release];
        _columnNameToIndexMap = nil;
    }
    [super dealloc];
}

- (void)close
{
    sqlite3_finalize(*statement);
    statement = NULL;
}

- (BOOL)next
{
    if (statement == NULL)
        return NO;
    
    return (sqlite3_step(*statement) == SQLITE_ROW);
}

- (NSMutableDictionary *)columnNameToIndexMap
{
    if (!_columnNameToIndexMap) {
        _columnNameToIndexMap = [[NSMutableDictionary alloc] initWithCapacity:(NSUInteger)dataCount];
        int columnIdx = 0;
        for (columnIdx = 0; columnIdx < dataCount; columnIdx++) {
            [_columnNameToIndexMap setObject:[NSNumber numberWithInt:columnIdx]
                                      forKey:[[NSString stringWithUTF8String:sqlite3_column_name(*statement, columnIdx)] lowercaseString]];
        }
    }
    
    return _columnNameToIndexMap;
}

- (int)columnIndexForName:(NSString *)columnName
{
    columnName = [columnName lowercaseString];
    
    NSNumber *n = [[self columnNameToIndexMap] objectForKey:columnName];
    
    if (n) {
        return [n intValue];
    }
    
    return -1;
}

- (int)intForColumn:(NSString *)columnName
{
    return [self intForColumnIndex:[self columnIndexForName:columnName]];
}

- (int)intForColumnIndex:(int)columnIdx
{
    return sqlite3_column_int(*statement, columnIdx);
}

- (long)longForColumn:(NSString *)columnName
{
    return [self longForColumnIndex:[self columnIndexForName:columnName]];
}

- (long)longForColumnIndex:(int)columnIdx
{
    return sqlite3_column_int64(*statement, columnIdx);
}

- (long long int)longLongIntForColumn:(NSString *)columnName
{
    return [self longLongIntForColumnIndex:[self columnIndexForName:columnName]];
}

- (long long int)longLongIntForColumnIndex:(int)columnIdx
{
    return sqlite3_column_int64(*statement, columnIdx);
}

- (unsigned long long int)unsignedLongLongIntForColumn:(NSString *)columnName
{
    return [self unsignedLongLongIntForColumnIndex:[self columnIndexForName:columnName]];
}

- (unsigned long long int)unsignedLongLongIntForColumnIndex:(int)columnIdx
{
    return sqlite3_column_int64(*statement, columnIdx);
}

- (BOOL)boolForColumn:(NSString *)columnName
{
    return [self boolForColumnIndex:[self columnIndexForName:columnName]];
}

- (BOOL)boolForColumnIndex:(int)columnIdx
{
    return (sqlite3_column_int(*statement, columnIdx) != 0);
}

- (double)doubleForColumn:(NSString *)columnName
{
    return [self doubleForColumnIndex:[self columnIndexForName:columnName]];
}

- (double)doubleForColumnIndex:(int)columnIdx
{
    return sqlite3_column_double(*statement, columnIdx);
}

- (NSString *)stringForColumn:(NSString *)columnName
{
    return [self stringForColumnIndex:[self columnIndexForName:columnName]];
}

- (NSString *)stringForColumnIndex:(int)columnIdx
{
    if (sqlite3_column_type(*statement, columnIdx) == SQLITE_NULL || (columnIdx < 0)) {
        return nil;
    }
    
    const char *c = (const char *)sqlite3_column_text(*statement, columnIdx);
    
    if (!c) {
        return nil;
    }
    
    return [NSString stringWithUTF8String:c];
}

- (NSDate *)dateForColumn:(NSString *)columnName
{
    return [self dateForColumnIndex:[self columnIndexForName:columnName]];
}

- (NSDate *)dateForColumnIndex:(int)columnIdx
{
    if (sqlite3_column_type(*statement, columnIdx) == SQLITE_NULL || (columnIdx < 0)) {
        return nil;
    }
    
    NSDateFormatter *dateFormat = [[NSDateFormatter alloc] init];
    [dateFormat setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
    
    return [dateFormat dateFromString:[NSString stringWithUTF8String:(char *)sqlite3_column_text(*statement, columnIdx)]];
}

- (NSData *)dataForColumn:(NSString *)columnName
{
    return [self dataForColumnIndex:[self columnIndexForName:columnName]];
}

- (NSData *)dataForColumnIndex:(int)columnIdx
{
    if (sqlite3_column_type(*statement, columnIdx) == SQLITE_NULL || (columnIdx < 0)) {
        return nil;
    }
    
    int dataSize = sqlite3_column_bytes(*statement, columnIdx);
    
    NSMutableData *data = [NSMutableData dataWithLength:(NSUInteger)dataSize];
    
    memcpy([data mutableBytes], sqlite3_column_blob(*statement, columnIdx), dataSize);
    
    return data;
}

- (NSData *)dataNoCopyForColumn:(NSString *)columnName
{
    return [self dataNoCopyForColumnIndex:[self columnIndexForName:columnName]];
}

- (NSData *)dataNoCopyForColumnIndex:(int)columnIdx
{
    if (sqlite3_column_type(*statement, columnIdx) == SQLITE_NULL || (columnIdx < 0)) {
        return nil;
    }
    
    int dataSize = sqlite3_column_bytes(*statement, columnIdx);
    
    NSData *data = [NSData dataWithBytesNoCopy:(void *)sqlite3_column_blob(*statement, columnIdx) length:(NSUInteger)dataSize freeWhenDone:NO];
    
    return data;
}

@end
